package fu.capstone.hanoicityguide.dataaccess.base;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	public static final String TAG = "DBAdapter";
	public static final String DATABASE_NAME = "HCG_DB";

	public static final int DATABASE_VERSION = 1;
	public static final String CREATE_TABLE_TYPE = "CREATE TABLE [Type] ([Id] integer NOT NULL , [CategoryId] integer NOT NULL , [TypeName] nvarchar(100) NOT NULL , PRIMARY KEY ([Id]) ,FOREIGN KEY ([CategoryId]) REFERENCES [Category]([Id]) );";
	public static final String CREATE_TABLE_CATEGORY = "CREATE TABLE [Category] ([Id]	integer NOT NULL,[CategoryName]	nvarchar(100) NOT NULL , PRIMARY KEY ([Id]));";
	public static final String CREATE_TABLE_ALBUMIMAGE = "CREATE TABLE [AlbumImage] ([Id] integer NOT NULL , [IconPath] nvarchar(1000) NOT NULL , [ImagePath1] nvarchar(1000) , [ImagePath2] nvarchar(1000) , [ImagePath3] nvarchar(1000) , [ImagePath4] nvarchar(1000) , [ImagePath5] nvarchar(1000) , PRIMARY KEY ([Id]));";
	public static final String CREATE_TABLE_GEOLOCATION = "CREATE TABLE [Geolocation] ([Id] integer NOT NULL,[Longitude] float NOT NULL,[Latitude] float NOT NULL, PRIMARY KEY ([Id]));";
	public static final String CREATE_TABLE_COMMONDIALOGUE = "CREATE TABLE [CommonDialogue] ([Id] integer NOT NULL,[EnglishContent] nvarchar(500) COLLATE NOCASE,[VietnameseContent] nvarchar(500) COLLATE NOCASE,[AudioPath] nvarchar(100) COLLATE NOCASE,PRIMARY KEY ([Id]));";
	public static final String CREATE_TABLE_ITINERARY = "CREATE TABLE [Itinerary] ([Id] integer NOT NULL , [ItineraryName] nvarchar(100) NOT NULL , [RatingId] integer NOT NULL ,[AlbumImageId] integer NOT NULL , [DBVersionId] integer NOT NULL ,PRIMARY KEY ([Id]) ,FOREIGN KEY ([RatingId]) REFERENCES [Rating]([Id]),FOREIGN KEY ([AlbumImageId]) REFERENCES [AlbumImage]([Id]),FOREIGN KEY ([DBVersionId]) REFERENCES [DBVersion]([Id]) );";
	public static final String CREATE_TABLE_CLASS = "CREATE TABLE [Class] ([Id] integer NOT NULL,[ClassName] nvarchar(50) ,PRIMARY KEY ([Id]));";
	public static final String CREATE_TALBE_DBVERSION = "CREATE TABLE [DBVersion] ([Id] integer NOT NULL,[VersionName] nvarchar(50) NOT NULL,[UpdateStatus] nvarchar(50) NOT NULL,PRIMARY KEY ([Id]));";
	public static final String CREATE_TABLE_RATING = "CREATE TABLE [Rating] ([Id] integer NOT NULL,[RatingValue] integer NOT NULL,PRIMARY KEY ([Id]));";
	public static final String CREATE_TABLE_HOME = "CREATE TABLE [Home] ([Id] integer NOT NULL,[Overview] nvarchar(2000) NOT NULL,[History] nvarchar(2000) NOT NULL, [AlbumImageId] integer NOT NULL , PRIMARY KEY ([Id]));";

	// public static final String CREATE_TABLE_ALBUMDETAIL =
	// "CREATE TABLE [AlbumDetail] ([AlbumId] integer,[ImagePath] nvarchar(200) NOT NULL COLLATE NOCASE, PRIMARY KEY ([ImagePath]),FOREIGN KEY ([AlbumId]) REFERENCES [AlbumImage]([Id]));";

	public static final String CREATE_TABLE_PLACE = "CREATE TABLE "
			+ "[Place] " + "([Id] integer NOT NULL, "
			+ "[Name] nvarchar(100) NOT NULL, " + "[GeoId] integer NOT NULL, "
			+ "[CategoryId] integer NOT NULL, " + "[TypeId] integer NOT NULL, "
			+ "[AlbumImageId] integer NOT NULL, "
			+ "[RatingId] integer NOT NULL, " + "[ClassId] integer , "
			+ "[Price] float, " + "[Description] nvarchar(2000) NOT NULL, "
			+ "[OpenTime] nvarchar(100) ," + "[CloseTime] nvarchar(100) ,"
			+ "[Address] nvarchar(100) NOT NULL,"
			+ "[PhoneNumber] nvarchar(50) ," + "[Website] nvarchar(50) ,"
			+ "[DBVersionId] integer NOT NULL, " + "PRIMARY KEY ([Id]), "
			+ "FOREIGN KEY ([AlbumImageId]) REFERENCES [AlbumImage]([Id]), "
			+ "FOREIGN KEY ([CategoryId]) REFERENCES [Category]([Id]), "
			+ "FOREIGN KEY ([ClassId]) REFERENCES [Class]([Id]), "
			+ "FOREIGN KEY ([GeoId]) REFERENCES [Geolocation]([Id]), "
			+ "FOREIGN KEY ([RatingId]) REFERENCES [Rating]([Id]), "
			+ "FOREIGN KEY ([DBVersionId]) REFERENCES [DBVersion]([Id]), "
			+ "FOREIGN KEY ([TypeId]) REFERENCES [Type]([Id]));";

	public static final String CREATE_TABLE_ITINERARYDETAIL = "CREATE TABLE "
			+ "[ItineraryDetail] " + "([ItineraryId] integer NOT NULL "
			+ ",[PlaceId] integer NOT NULL " + ",[PlaceOrder] integer NOT NULL"
			+ ",[DBVersionId] integer NOT NULL"
			+ ", PRIMARY KEY ([ItineraryId] , [PlaceId]) "
			+ ", FOREIGN KEY ([ItineraryId]) REFERENCES [Itinerary]([Id]) "
			+ ", FOREIGN KEY ([PlaceId]) REFERENCES [Place]([Id]));";

	// Food gom: id, name, desc, ratingid, albumid (album gom icon + anh). Giong
	// Holiday
	public static final String CREATE_TABLE_FOOD = "CREATE TABLE Food ("
			+ "[Id] integer NOT NULL Primary key, "
			+ "[Name] nvarchar(100) NOT NULL, "
			+ "[Description] nvarchar(2000) NOT NULL, "
			+ "[RatingId] integer NOT NULL," + "AlbumImageId integer NOT NULL,"
			+ "[GeoId] integer NOT NULL," + "[Price] float NOT NULL,"
			+ "[Address] nvarchar(100) NOT NULL,"
			+ "[PhoneNumber] nvarchar(50) NOT NULL,"
			+ " FOREIGN KEY ([RatingId]) REFERENCES Rating([Id]), "
			+ " FOREIGN KEY ([GeoId]) REFERENCES Geolocation([Id]), "
			+ " FOREIGN KEY ([AlbumImageId]) REFERENCES AlbumImage([Id]));";

	public static final String CREATE_TABLE_HOLIDAY = "CREATE TABLE Holiday ("
			+ "[Id] integer NOT NULL PRIMARY KEY, "
			+ "[Name] nvarchar(100) NOT NULL , "
			+ "[Description] nvarchar(2000) NOT NULL, "
			+ "[AlbumImageId] integer NOT NULL,"
			+ " FOREIGN KEY ([AlbumImageID]) REFERENCES AlbumImage([Id]));";

	public static final String CREATE_TABLE_TRANSPORT_CATEGORY = "CREATE TABLE TransportCategory ( "
			+ "[Id] integer NOT NULL Primary key, "
			+ "[TransportName] nvarchar(50));";

	public static final String CREATE_TABLE_TRANSPORT = "CREATE TABLE Transport ("
			+ "[Id] integer NOT NULL Primary key, "
			+ "[TransportId] integer, "
			+ "[Name] nvarchar(100), "
			+ "[Description] nvarchar(2000), "
			+ "[FirstLeg] nvarchar(2000), "
			+ "[SecondLeg] nvarchar(2000), "
			+ "[Price] nvarchar(10),"
			+ "[PhoneNumber] nvarchar(15), "
			+ "[Website] nvarchar(50), "
			+ "[IconPath] nvarchar(500), "
			+ "FOREIGN KEY ([TransportId]) REFERENCES TransportCategory([Id]));";

	public static final String CREATE_TABLE_TOUR_PROVIDER = "CREATE TABLE [TourProvider] ([Id] integer NOT NULL , [Name] nvarchar(100) NOT NULL , [IconPath] nvarchar(1000) NOT NULL , [Address] nvarchar(100) NOT NULL , [Website] nvarchar(50) , [PhoneNumber] nvarchar(50) , [DBVersionId] integer NOT NULL , PRIMARY KEY ([Id]) ,FOREIGN KEY ([DBVersionId]) REFERENCES [DBVersion]([Id]) );";

	public static final String CREATE_TABLE_TOUR = "CREATE TABLE " + "[Tour] "
			+ "([Id] integer NOT NULL " + ",[Name] nvarchar(100) NOT NULL "
			+ ",[ProviderId] integer NOT NULL "
			+ ",[RatingId] integer NOT NULL "
			+ ",[AlbumImageId] integer NOT NULL " + ",[Price] float NOT NULL "
			+ ",[Duration] float NOT NULL "
			+ ",[Description] nvarchar(1000) NOT NULL "
			+ ",[DBVersionId] integer NOT NULL" + ", PRIMARY KEY ([Id]) "
			+ ", FOREIGN KEY ([ProviderId]) REFERENCES [TourProvider]([Id]) "
			+ ", FOREIGN KEY ([RatingId]) REFERENCES [Rating]([Id]) "
			+ ", FOREIGN KEY ([AlbumImageId]) REFERENCES [AlbumImage]([Id]) "
			+ ", FOREIGN KEY ([DBVersionId]) REFERENCES [DBVersion]([Id]));";

	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(CREATE_TABLE_HOME);
		db.execSQL(CREATE_TABLE_TYPE);
		db.execSQL(CREATE_TABLE_CATEGORY);
		db.execSQL(CREATE_TABLE_ALBUMIMAGE);
		db.execSQL(CREATE_TABLE_GEOLOCATION);
		db.execSQL(CREATE_TABLE_COMMONDIALOGUE);
		db.execSQL(CREATE_TABLE_ITINERARY);
		db.execSQL(CREATE_TABLE_CLASS);
		db.execSQL(CREATE_TALBE_DBVERSION);
		db.execSQL(CREATE_TABLE_RATING);
		db.execSQL(CREATE_TABLE_PLACE);
		db.execSQL(CREATE_TABLE_ITINERARYDETAIL);
		db.execSQL(CREATE_TABLE_FOOD);
		db.execSQL(CREATE_TABLE_HOLIDAY);
		db.execSQL(CREATE_TABLE_TOUR_PROVIDER);
		db.execSQL(CREATE_TABLE_TOUR);
		db.execSQL(CREATE_TABLE_TRANSPORT_CATEGORY);
		db.execSQL(CREATE_TABLE_TRANSPORT);
		Log.v("Create Database ....", "Create new DB");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		Log.v("old Version DB", String.valueOf(oldVersion));
		Log.v("new Version DB", String.valueOf(newVersion));
		if (oldVersion < newVersion) {
			Log.i(TAG, "Upgrading DB");
			db.execSQL("DROP TABLE IF EXISTS " + "ItineraryDetail");
			db.execSQL("DROP TABLE IF EXISTS " + "Place");
			db.execSQL("DROP TABLE IF EXISTS " + "Rating");
			db.execSQL("DROP TABLE IF EXISTS " + "DBVersion");
			db.execSQL("DROP TABLE IF EXISTS " + "Class");
			db.execSQL("DROP TABLE IF EXISTS " + "Itinerary");
			db.execSQL("DROP TABLE IF EXISTS " + "CommonDialogue");
			db.execSQL("DROP TABLE IF EXISTS " + "GeoLocation");
			db.execSQL("DROP TABLE IF EXISTS " + "AlbumImage");
			db.execSQL("DROP TABLE IF EXISTS " + "Category");
			db.execSQL("DROP TABLE IF EXISTS " + "Type");
			db.execSQL("DROP TABLE IF EXISTS " + "Food");
			db.execSQL("DROP TABKE IF EXISTS " + "Holiday");
			db.execSQL("DROP TABLE IF EXISTS " + "Home");
			db.execSQL("DROP TABLE IF EXISTS " + "Tour");
			db.execSQL("DROP TABLE IF EXISTS " + "TourProvider");
			db.execSQL("DROP TABKE IF EXISTS " + "Transport");
			db.execSQL("DROP TABLE IF EXISTS " + "TransportCategory");
			// db.execSQL(CREATE_TABLE_TRANSPORT_CATEGORY);
			// db.execSQL(CREATE_TABLE_TRANSPORT);
			onCreate(db);
		} else {

		}

	}
}
